package com.bds.wm.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bds.wm.model.procmodel.*;
import com.bds.wm.utils.FormatUils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.log4j.Logger;
import org.nutz.dao.Sqls;
import org.nutz.dao.impl.NutDao;
import org.nutz.dao.sql.Sql;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by guoyu on 2018/6/1.
 */
@Service
public class BrandViewService {
    private Logger LOGGER = Logger.getLogger(BrandViewService.class);
    @Resource
    private NutDao dao;

    public BrandOverviewResult getBrandOverviewResult(int startMonth, int endMonth, String brandName, String subcatName) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandOverviewResult @StartMonth,@EndMonth," +
                "@SubCategoryName,@BrandName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandOverviewResult.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("SubCategoryName", subcatName); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        dao.execute(sql);
        return sql.getObject(BrandOverviewResult.class);
    }

    public BrandOverviewResultRate getBrandOverviewResultRate(int startMonth, int endMonth,
                                                              String brandName, String subcatName) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandOverviewResultRate @StartMonth,@EndMonth," +
                "@SubCategoryName,@BrandName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandOverviewResultRate.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("SubCategoryName", subcatName); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        dao.execute(sql);
        return sql.getObject(BrandOverviewResultRate.class);
    }

    public List<BrandMapOverviewResult> getBrandMapOverviewResult(int startMonth, int endMonth,
                                                                  String brandName, String subCatName,
                                                                  double maxLng, double minLng,
                                                                  double maxLat, double minLat) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandMapOverviewResult @StartMonth,@EndMonth," +
                "@SubCategoryName,@BrandName,@MinLongitude,@MaxLongitude,@MinLatitude,@MaxLatitude");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandMapOverviewResult.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("SubCategoryName", subCatName); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        sql.params().set("MinLongitude", minLng); //设置入参
        sql.params().set("MaxLongitude", maxLng); //设置入参
        sql.params().set("MinLatitude", minLat); //设置入参
        sql.params().set("MaxLatitude", maxLat); //设置入参
        dao.execute(sql);
        return sql.getList(BrandMapOverviewResult.class);
    }

    public List<BrandMapOverviewResultByCapacity> getBrandMapOverviewResultByCapacity(
            int startMonth, int endMonth, String brandName, String subCatName, float realLng, float realLat) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getMapOverviewResultbyCapacity @EndMonth,@BrandName,@lon,@lat");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandMapOverviewResultByCapacity.class));
        sql.params().set("BrandName", brandName); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("lon", realLng); //设置入参
        sql.params().set("lat", realLat); //设置入参
        dao.execute(sql);
        return sql.getList(BrandMapOverviewResultByCapacity.class);
    }

    public JSONObject getBrandOverviewResultByCity(int startMonth, int endMonth, String brandName) {
        JSONObject jsonObject = new JSONObject();
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandOverviewResultbyCityTest @StartMonth,@EndMonth," +
                "@Segment");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandOverviewResultByBrandName.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("Segment", brandName); //设置入参
        dao.execute(sql);
        List<BrandOverviewResultByBrandName> olds = sql.getList(BrandOverviewResultByBrandName.class);
        //拼接BrandOverviewResultByCity对象,修改改名的字段
        List<BrandOverviewResultByCity> list=Lists.newArrayList();
        olds.forEach(old->{
            if(!"total".equals(old.getSubSegment())) {//第一个数据为全部汇总，暂时不用
                String json = JSONObject.toJSONString(old);
                BrandOverviewResultByCity overview = JSONObject.parseObject(json, BrandOverviewResultByCity.class);
                overview.setTierName(old.getCitytier());
                overview.setCityName(old.getSubSegment());
                overview.setSubCategoryName(old.getSubSegment());
                overview.setBrandName(old.getSegment());//品牌
                list.add(overview);
            }
        });
        Map<String, String[]> cateMap = new LinkedHashMap<>();//分组
        Map<String, List<String[]>> subCateMap = new HashMap<>();//分组
        list.forEach(cate -> {
            try {
                BigDecimal brandTCPer = BigDecimal.ZERO;
                BigDecimal brandTCCAGR = BigDecimal.ZERO;
                BigDecimal brandSalesValuePer = BigDecimal.ZERO;
                BigDecimal brandSalesValueCAGR = BigDecimal.ZERO;
                if (cate.getBrandTCPer() != null) {
                    brandTCPer = new BigDecimal(cate.getBrandTCPer()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getBrandTCCAGR() != null) {
                    brandTCCAGR = new BigDecimal(cate.getBrandTCCAGR()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getBrandSalesValuePer() != null) {
                    brandSalesValuePer = new BigDecimal(
                            cate.getBrandSalesValuePer()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getBrandSalesValueCAGR() != null) {
                    brandSalesValueCAGR = new BigDecimal(
                            cate.getBrandSalesValueCAGR()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getTierName()==null) {//城市级别为空时，说明是母表数据;不空为子表数据
                    String[] row = new String[]{
                            cate.getTierName()==null?cate.getSubCategoryName():cate.getTierName(),
                            cate.getTierName()==null?cate.getSubCategoryName():cate.getTierName(),
                            FormatUils.formatConv(Math.round(cate.getBrandStoreNo()) + ""),//13品类店铺数
                            FormatUils.formatConv(cate.getBrandTC(), 10000),//14品类客单数(万)
                            FormatUils.formatConv(brandTCPer.doubleValue()),//15品类客单数占比
                            FormatUils.formatConv(cate.getAvgStoreTC()),//品类店均客单数(万)
                            FormatUils.formatConv(brandTCCAGR.doubleValue()),//客单数增长率
                            FormatUils.formatConv(cate.getBrandSalesValue(), 100000000),//销售额（亿）
                            FormatUils.formatConv(brandSalesValuePer.doubleValue()),//16品类销售额占比
                            FormatUils.formatConv(cate.getAvgStoreaValue(), 10000),//17品类销售额（万元）
                            FormatUils.formatConv(brandSalesValueCAGR.doubleValue()),//18品类销售额增长率
                            FormatUils.formatConv(cate.getBrandTA())//品类客单价
                    };
                    cateMap.put(cate.getSubCategoryName(), row);//城市级别为空时，存储过程的城市级别存在了城市这一列
                } else {//拼接子表数据
                    String[] subRow = new String[]{
                            cate.getCityName(),
                            FormatUils.formatConv(Math.round(cate.getBrandStoreNo()) + ""),//13品类店铺数
                            FormatUils.formatConv(cate.getBrandTC(), 10000),//14品类客单数(万)
                            FormatUils.formatConv(brandTCPer.doubleValue()),//15品类客单数占比
                            FormatUils.formatConv(cate.getAvgStoreTC()),//品类店均客单数(万)
                            FormatUils.formatConv(brandTCCAGR.doubleValue()),//客单数增长率
                            FormatUils.formatConv(cate.getBrandSalesValue(), 100000000),//销售额（亿）
                            FormatUils.formatConv(brandSalesValuePer.doubleValue()),//16品类销售额占比
                            FormatUils.formatConv(cate.getAvgStoreaValue(), 10000),//17品类销售额（万元）
                            FormatUils.formatConv(brandSalesValueCAGR.doubleValue()),//18品类销售额增长率
                            FormatUils.formatConv(cate.getBrandTA())//品类客单价
                    };
                    List<String[]> subRows = subCateMap.get(cate.getTierName());
                    if(subRows==null) {
                        subRows = new ArrayList<>();
                    }
                    subRows.add(subRow);
                    subCateMap.put(cate.getTierName(), subRows);//城市级别作为键
                }
            } catch (Exception e) {
                LOGGER.warn("[service]-[33]-getBrandOverviewResultByCity获取品牌信息异常", e);
            }
        });
        //母表数据 [['QSR','Total','1000','50'],[]]
        jsonObject.put("cate", cateMap.values());
        //子表数据
        jsonObject.put("subCate", subCateMap);

        return jsonObject;
    }
    public JSONObject getBrandOverviewResultByCity2(int startMonth, int endMonth, String brandName, String subCatName) {
        JSONObject jsonObject = new JSONObject();

        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandOverviewResultbyCity @StartMonth,@EndMonth," +
                "@SubCategoryName,@BrandName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandOverviewResultByCity.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("SubCategoryName", subCatName); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        dao.execute(sql);
        List<BrandOverviewResultByCity> list = sql.getList(BrandOverviewResultByCity.class);

        Map<String, String[]> cateMap = new LinkedHashMap<>();//分组
        Map<String, List<String[]>> subCateMap = new HashMap<>();//分组
        list.forEach(cate -> {
            try {
                //if(StringUtils.isNotEmpty(cate.getSectionName())&&!"Null".equals(cate.getSectionName())) {
                BigDecimal brandTCPer = BigDecimal.ZERO;
                BigDecimal brandTCCAGR = BigDecimal.ZERO;
                BigDecimal brandSalesValuePer = BigDecimal.ZERO;
                BigDecimal brandSalesValueCAGR = BigDecimal.ZERO;
                if (cate.getBrandTCPer() != null) {
                    brandTCPer = new BigDecimal(cate.getBrandTCPer()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getBrandTCCAGR() != null) {
                    brandTCCAGR = new BigDecimal(cate.getBrandTCCAGR()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getBrandSalesValuePer() != null) {
                    brandSalesValuePer = new BigDecimal(
                            cate.getBrandSalesValuePer()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getBrandSalesValueCAGR() != null) {
                    brandSalesValueCAGR = new BigDecimal(
                            cate.getBrandSalesValueCAGR()).multiply(BigDecimal.valueOf(100));
                }
                String[] subRow = new String[]{
                        cate.getCityName(),
                        FormatUils.formatConv(Math.round(cate.getBrandStoreNo()) + ""),//13品类店铺数
                        FormatUils.formatConv(cate.getBrandTC(), 10000),//14品类客单数(万)
                        FormatUils.formatConv(brandTCPer.doubleValue()),//15品类客单数占比
                        FormatUils.formatConv(cate.getAvgStoreTC()),//品类店均客单数(万)
                        FormatUils.formatConv(brandTCCAGR.doubleValue()),//客单数增长率
                        FormatUils.formatConv(cate.getBrandSalesValue(), 100000000),//销售额（亿）
                        FormatUils.formatConv(brandSalesValuePer.doubleValue()),//16品类销售额占比
                        FormatUils.formatConv(cate.getAvgStoreaValue(), 10000),//17品类销售额（万元）
                        FormatUils.formatConv(brandSalesValueCAGR.doubleValue()),//18品类销售额增长率
                        FormatUils.formatConv(cate.getBrandTA())//品类客单价
                };
                List<String[]> subRows = null;
                if (!cateMap.containsKey(cate.getTierName())) {
                    subRows = new ArrayList<>();
                    BigDecimal totBrandTCPer = BigDecimal.ZERO;
                    BigDecimal totBrandTCCAGR = BigDecimal.ZERO;
                    BigDecimal totBrandSalesValuePer = BigDecimal.ZERO;
                    BigDecimal totBrandSalesValueCAGR = BigDecimal.ZERO;
                    if (cate.getTotBrandTCPer() != null) {
                        totBrandTCPer = new BigDecimal(cate.getTotBrandTCPer()).multiply(BigDecimal.valueOf(100));
                    }
                    if (cate.getTotBrandTCCAGR() != null) {
                        totBrandTCCAGR = new BigDecimal(cate.getTotBrandTCCAGR()).multiply(BigDecimal.valueOf(100));
                    }
                    if (cate.getTotBrandSalesValuePer() != null) {
                        totBrandSalesValuePer = new BigDecimal(
                                cate.getTotBrandSalesValuePer()).multiply(BigDecimal.valueOf(100));
                    }
                    if (cate.getTotBrandSalesValueCAGR() != null) {
                        totBrandSalesValueCAGR = new BigDecimal(
                                cate.getTotBrandSalesValueCAGR()).multiply(BigDecimal.valueOf(100));
                    }
                    String[] row = new String[]{
                            cate.getTierName(),
                            cate.getTierName(),
                            FormatUils.formatConv(Math.round(cate.getTotBrandStoreNo()) + ""),//13品类店铺数
                            FormatUils.formatConv(cate.getTotBrandTC(), 10000),//14品类客单数(万)
                            FormatUils.formatConv(totBrandTCPer.doubleValue()),//15品类客单数占比
                            FormatUils.formatConv(cate.getAvgTotStoreTC()),//品类店均客单数(万)
                            FormatUils.formatConv(totBrandTCCAGR.doubleValue()),//客单数增长率
                            FormatUils.formatConv(cate.getTotBrandSalesValue(), 100000000),//销售额（亿）
                            FormatUils.formatConv(totBrandSalesValuePer.doubleValue()),//16品类销售额占比
                            FormatUils.formatConv(cate.getTotAvgStoreaValue(), 10000),//17品类店均销售额（万元）
                            FormatUils.formatConv(totBrandSalesValueCAGR.doubleValue()),//18品类销售额增长率
                            FormatUils.formatConv(cate.getTotBrandTA())//品类客单价
                    };
                    cateMap.put(cate.getTierName(), row);
                } else {
                    subRows = subCateMap.get(cate.getTierName());
                }
                subRows.add(subRow);
                subCateMap.put(cate.getTierName(), subRows);
                //}
            } catch (Exception e) {
                LOGGER.warn("[service]-[33]-getBrandOverviewResultByCity获取品牌信息异常", e);
            }
        });
        jsonObject.put("cate", cateMap.values());
        jsonObject.put("subCate", subCateMap);

        return jsonObject;
    }

    public JSONObject getBrandOverviewResultByProduct(int startMonth, int endMonth, String brandName,String category) {
        JSONObject jsonObject = new JSONObject();

        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandOverviewResultbyProductTest @StartMonth,@EndMonth,@BrandName,@Category");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandOverviewResultByProduct.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        sql.params().set("Category", category); //设置入参
        dao.execute(sql);
        List<BrandOverviewResultByProduct> list = sql.getList(BrandOverviewResultByProduct.class);

        if (list.size() > 0) {
            jsonObject.put("product_list", new JSONObject());
            JSONArray typeList = new JSONArray();
            String currentType = "", preType = "";
            for (BrandOverviewResultByProduct b : list) {
                currentType = b.getProductType();
                if (!currentType.equals(preType)) {
                    typeList.add(currentType);
                    jsonObject.getJSONObject("product_list").put(currentType, new JSONArray());
                }
                preType = b.getProductType();
                jsonObject.getJSONObject("product_list").getJSONArray(currentType).add(b);
            }
            jsonObject.put("product_type", typeList);
        }

        return jsonObject;
    }

    public List<BrandOverviewResultByCityStore> getBrandOverviewResultbycitystore(String start_month,
                                                        String end_month, String brand_name,
                                                        String subcat_name,
                                                        String cityTier, String cityname
                                                        ){

        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandOverviewResultbycitystore " +
                "@StartMonth ,@EndMonth ,@BrandName ,@cityname ");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandOverviewResultByCityStore.class));
        sql.params().set("StartMonth", start_month); //设置入参
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName", brand_name); //设置入参
        sql.params().set("cityname", cityname); //设置入参
        dao.execute(sql);
        return sql.getList(BrandOverviewResultByCityStore.class);
    }


    public JSONObject getBrandStoreCommentRatio(String start_month, String end_month, String brandName, String city) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandStoreCommentRatioTest @StartMonth, @EndMonth, @BrandName, @city");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandStoreCommentRatioNew.class));
        sql.params().set("StartMonth", start_month); //设置入参
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("city", city); //设置入参
        dao.execute(sql);
        JSONObject jsonObject = new JSONObject();
        List<BrandStoreCommentRatioNew> old = sql.getList(BrandStoreCommentRatioNew.class);
        //重新 1.给query字段赋值，新增n，p   取代per字段  2.喜好和同比增长这两列取_p得数据
        Map<String,BrandStoreCommentRatioNewModel> newRatio= Maps.newHashMap();
        old.forEach(cate -> {
            String obj = JSONObject.toJSONString(cate);
            String[] res = cate.getQuery().split("_");
            String query=(res.length>2)?res[0]+"_"+res[1]:res[0];//把n，p两条数据合成一条
            BrandStoreCommentRatioNewModel model =null;
            if(newRatio.containsKey(query)){
                model=newRatio.get(query);
            }else{
                model = JSONObject.parseObject(obj, BrandStoreCommentRatioNewModel.class);
            }
            model.setQuery(query);
            if(Arrays.asList(res).contains("N")){
                model.setPerN(cate.getPer());
            }else{//子表数据，喜好和同比增长这两列取_p得数据
                model.setSR(cate.getSR());
                model.setSRRate(cate.getSRRate());
                model.setPerP(cate.getPer());
            }
            newRatio.put(query,model);
        });
        //拼接前台model
        Map<String, String[]> cateMap = new HashMap<>();//分组
        Map<String, List<String[]>> subCateMap = new HashMap<>();//分组
        newRatio.values().forEach(cate -> {
            try {
                BigDecimal PerP = BigDecimal.ZERO;
                BigDecimal PerN = BigDecimal.ZERO;
                BigDecimal SR = BigDecimal.ZERO;
                BigDecimal SRRate = BigDecimal.ZERO;
                if (cate.getPerN() != null) {
                    PerN = new BigDecimal(cate.getPerN()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getPerP() != null) {
                    PerP = new BigDecimal(cate.getPerP()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getSR() != null) {
                    SR = new BigDecimal(cate.getSR()).multiply(BigDecimal.valueOf(100));
                }
                if (cate.getSRRate() != null) {
                    SRRate = new BigDecimal(
                            cate.getSRRate()).multiply(BigDecimal.valueOf(100));
                }

                String word = convert2word(cate.getQuery());
                String[] tag = word.split("_");
                if("1".equals(tag[0])) {
                    //拼接母表数据('','讨论满意度占比','正面喜好度%', '同比增长趋势%')
                    String[] row = new String[]{
                            tag[1],//1分类
                            FormatUils.formatConv(PerN.doubleValue())+"/"+FormatUils.formatConv(PerP.doubleValue()),//2讨论满意度占比
                            FormatUils.formatConv(SR.doubleValue()),//3正面喜好度%
                            FormatUils.formatConv(SRRate.doubleValue())//4.同比增长趋势%
                    };
                    cateMap.put(tag[1], row);
                }else {
                    //拼接子表数据
                    String[] subRow = new String[]{
                            tag[1],
                            FormatUils.formatConv(PerN.doubleValue()) + "/" + FormatUils.formatConv(PerP.doubleValue()),//2讨论满意度占比
                            FormatUils.formatConv(SR.doubleValue()),//3正面喜好度%
                            FormatUils.formatConv(SRRate.doubleValue())//4.同比增长趋势%
                    };
                    //查找该条子表数据对应的父 query
                    String parentQuery = convert2word(cate.getQuery().split("_")[0]).split("_")[1];
                    List<String[]> subRows = subCateMap.get(parentQuery);
                    if (subRows == null) {
                        subRows = new ArrayList<>();
                    }
                    subRows.add(subRow);
                    subCateMap.put(parentQuery, subRows);//城市级别作为键
                }
            } catch (Exception e) {
                LOGGER.warn("[service]-[33]-getBrandOverviewResultByCity获取品牌信息异常", e);
            }
        });
        //母表数据 [['QSR','Total','1000','50'],[]]
        jsonObject.put("cate", cateMap.values());
        //子表数据
        jsonObject.put("subCate", subCateMap);

        return jsonObject;

    }

    public BrandStoreCommentRatio getBrandStoreCommentRatio2(String start_month, String end_month, String brandName, String city) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandStoreCommentRatio @StartMonth, @EndMonth, @BrandName, @city");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandStoreCommentRatio.class));
        sql.params().set("StartMonth", start_month); //设置入参
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("city", city); //设置入参
//        sql.params().set("RestaurantName", restaurantName); //设置入参
        dao.execute(sql);
        return sql.getObject(BrandStoreCommentRatio.class);
    }
    public List<BrandCityStoreName> getBrandCityStoreName(int end_month, String brand_name) {
        Sql sql = Sqls.fetchEntity("exec Report.getBrandCityStoreName " +
                "@EndMonth ,@BrandName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandCityStoreName.class));
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName", brand_name); //设置入参
        dao.execute(sql);
        return sql.getList(BrandCityStoreName.class);
    }

    public List<BrandMonthlyRatingTrend> getBrandMonthlyRatingTrend(String end_month, String brandName, String city) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandMonthlyRatingTrend @EndMonth, @BrandName, @city");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandMonthlyRatingTrend.class));
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("city", city); //设置入参
        dao.execute(sql);
        return sql.getList(BrandMonthlyRatingTrend.class);
    }

    public List<BrandStoreRating> getBrandStoreRating(String startMonth, String end_month,
                                                      String brandName, String city, String sortOrder) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandStoreRating @StartMonth ,@EndMonth " +
                ",@BrandName ,@city ,@SortOrder");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandStoreRating.class));
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("city", city); //设置入参
        sql.params().set("StartMonth",startMonth ); //设置入参
        sql.params().set("SortOrder", sortOrder); //设置入参

        dao.execute(sql);
        return sql.getList(BrandStoreRating.class);
    }


    public List<BrandStoreRatingTrend> getBrandStoreRatingTrend(String end_month,
                                                      String brandName, String city, String storeName) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandStoreRatingTrend " +
                "@EndMonth ,@BandName,@city,@StoreName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandStoreRatingTrend.class));
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BandName",brandName ); //设置入参
        sql.params().set("city", city); //设置入参
        sql.params().set("StoreName",storeName ); //设置入参
        dao.execute(sql);
        return sql.getList(BrandStoreRatingTrend.class);
    }

    public List<BrandKeyWord> getBrandKeyWord(String end_month,
                                                                String brandName, String city, String start_month) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getKeyBrandWordE " +
                "@StartMonth,@EndMonth,@BrandName,@city");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandKeyWord.class));
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("city", city); //设置入参
        sql.params().set("StartMonth",start_month ); //设置入参
        dao.execute(sql);
        List<BrandKeyWord> words1=sql.getList(BrandKeyWord.class);
        //创建SQL对象,用于调用存储过程
        Sql sql2 = Sqls.fetchEntity("exec Report.getKeyBrandWordP " +
                "@StartMonth,@EndMonth,@BrandName,@city");
        sql2.setCallback(Sqls.callback.entities());
        sql2.setEntity(dao.getEntity(BrandKeyWord.class));
        sql2.params().set("EndMonth", end_month); //设置入参
        sql2.params().set("BrandName",brandName ); //设置入参
        sql2.params().set("city", city); //设置入参
        sql2.params().set("StartMonth",start_month ); //设置入参
        dao.execute(sql2);
        List<BrandKeyWord> words2=sql2.getList(BrandKeyWord.class);
        List<BrandKeyWord> result = Lists.newArrayList(words2);
        words1.forEach(words->{
                words.setEmotion(1);
                result.add(words);
        });
        return result;
    }
    public List<BrandKeyWord> getBrandKeyWord2(String end_month,
                                              String brandName, String city, String start_month) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getBrandKeyWord " +
                "@StartMonth,@EndMonth,@BrandName,@city");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandKeyWord.class));
        sql.params().set("EndMonth", end_month); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("city", city); //设置入参
        sql.params().set("StartMonth",start_month ); //设置入参
        dao.execute(sql);
        return sql.getList(BrandKeyWord.class);
    }
    public BrandOverviewResultofOldStore getBrandOverviewResultofOldStore(Integer startMonth, Integer endMonth, String brandName) {
        Sql sql = Sqls.fetchEntity("exec Report.getBrandOverviewResultofOldStore @StartMonth,@EndMonth,@BrandName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandOverviewResultofOldStore.class));
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("StartMonth",startMonth ); //设置入参
        dao.execute(sql);
        return sql.getObject(BrandOverviewResultofOldStore.class);
    }

    public List<BrandNews> getBrandNews(Integer startMonth, Integer endMonth, String brandName) {
        Sql sql = Sqls.fetchEntity("exec Report.getBrandNews @BrandName,@StartMonth,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(BrandNews.class));
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("BrandName",brandName); //设置入参
        sql.params().set("StartMonth", startMonth); //设置入参
        dao.execute(sql);
        return sql.getList(BrandNews.class);
    }

    public String convert2word(String word){
        switch (word) {
            case "Food":
                    return "1_食品";
            case "Food_Quality":
                    return "2_食品质量";
            case "Food_Quantity":
                    return "2_食品数量";
            case "Food_Taste":
                    return "2_食品味道";
            case "Food_Temperature":
                    return "2_食品温度";
            case "Food_Variety":
                    return "2_食品种类";
            case "Price":
                    return "1_价格";
            case "Price_Pricing":
                    return "2_价格高低";
            case "Price_ServiceFee":
                    return "2_价格服务费";
            case "Price_Valueformoney":
                    return "2_价格划算";
            case "Service":
                    return "1_服务";
            case "Service_Servicespeed":
                    return "2_服务速度";
            case "Service_Attitude":
                    return "2_服务态度";
        }
        return null;
    }
}
